package com.scalar.chiptrack.adaptor.ikanos.sigurd;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.TestLotBinReport;
import com.scalar.chiptrack.adaptor.SortLotBinData;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.FabSortAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Date;
import java.io.File;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 17 Sep, 2009
 * Time: 4:46:52 PM
 * To change this template use File | Settings | File Templates.
 */
public class SigurdDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, FabSortLot> m_sortDataMap           = new HashMap<String, FabSortLot>();
    private HashMap<String, ShippedLot> m_sortShipDataMap       = new HashMap<String, ShippedLot>();
    private HashMap<String, InventoryLot> m_waferBankDataMap    = new HashMap<String, InventoryLot>();
    private HashMap<String, TestLot> m_testDataMap              = new HashMap<String, TestLot>();
    private HashMap<String, ShippedLot> m_testShipDataMap       = new HashMap<String, ShippedLot>();
    private HashMap<String, InventoryLot> m_preTestDataMap		= new HashMap<String, InventoryLot>();
    private HashMap<String, FabSortLot> m_sortBinDataMap    = new HashMap<String, FabSortLot>();
    private HashMap<String, FabSortLot> m_sortTotalMap      = new HashMap<String, FabSortLot>();

    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    public  SigurdDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir( VENDOR_NAME_SIGURD , WIP_TYPE_SORT );

            if ( m_dataOutputDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );

                m_parserLogger= AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_SIGURD );

                AdaptorUtils.setLogger( m_parserLogger );
                ShippedLotDAO.setLogger( m_parserLogger);
                InventoryLotDAO.setLogger(m_parserLogger);
                TestAdaptorDAO.setLogger( m_parserLogger);
                FabSortAdaptorDAO.setLogger( m_parserLogger);
            }
        }
        catch ( Exception se )
        {
            log( "ERROR!! ERROR!! ERROR!! Exception while loading Sigurd output directory details from database, Exception: " + se );
            System.out.println( "ERROR!! ERROR!! ERROR!! Exception while loading Sigurd output directory details from database, Exception: " + se );
        }
    }
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new SigurdDataParser().execute();
        }
        catch ( Exception e )
        {
            throw e;
        }
    }
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader( VENDOR_NAME_SIGURD, CONSTANT_PARSER );

        File doneDestDir = new File( m_dataOutputDir + "/done" );
        File dataoutDir  = new File( m_dataOutputDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || (fileList.length <= 3))
        {
            log("No file found in directory: " + dataoutDir);
            System.out.println("No file found in directory: " + dataoutDir );
            throw new ScalarException("No file found in directory: " + dataoutDir+ "----\n\n" );
        }

        ArrayList<File> successFileList = new ArrayList<File>();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {
                 if ( fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_FT_WIP )  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_FT_WIP )  != -1)
                {
                     parseTestWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_CP_WIP )  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_CP_WIP )  != -1)
                {
                     parseCPWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( (fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_SHIP )  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_SHIP )  != -1 )
                        && fileList[i].getName().indexOf( "CP" )  != -1 )
                {
                     parseCPShipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( ( fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_SHIP )  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_SHIP )  != -1)
                        && fileList[i].getName().indexOf( "FT" )  != -1 )
                {
                    parseFTShipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( (fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_YIELD )  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_YIELD )  != -1)
                        && fileList[i].getName().indexOf( "CP" )  != -1 )
                {
                     //For Saving the Sort Binning
                    ArrayList<SortLotBinData> sortBinList = parseCPBinReport( fileList[i].getAbsolutePath() );

                    if ( sortBinList != null && sortBinList.size() > 0 )
                    {
                        log("updateing the Sigurd Cp Binning Info....");
                        FabSortAdaptorDAO.saveSortBinInfo( null, sortBinList, VENDOR_NAME_SIGURD );
                        success = true;
                    }

                    successFileList.add(fileList[i]);
                }

                if ( (fileList[i].getName().startsWith( SigurdPOP3Adaptor.FILE_NAME_YIELD)  || fileList[i].getName().indexOf( SigurdPOP3Adaptor.FILE_NAME_YIELD )  != -1)
                        && fileList[i].getName().indexOf( "FT" )  != -1 )
                {
                     ArrayList<TestLotBinReport> testBinList = parseFTYieldReport( fileList[i].getAbsolutePath() );
                     if ( testBinList != null && testBinList.size() > 0 )
                     {
                        //to get the Sigurd Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating Sigurd FT Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SIGURD );
                            success = true;
                        }
                        else
                        {
                            log( "SIGURD FT Binning Information List is Empty" );
                        }
                    }
                    successFileList.add(fileList[i]);
                }

                success = true;
            }
            catch (Exception e)
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
        }

        DBTransaction dbTransaction = null;

        try
        {
            // Save Wafer Store Information
            if (m_waferBankDataMap.isEmpty() == false)
            {
                log(" Updating Wafer Store Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_waferBankDataMap.values().iterator(), LOCATION_TYPE_WAFER_BANK, VENDOR_NAME_SIGURD);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_WAFER_BANK, VENDOR_NAME_SIGURD);
            }
            success = true;
            m_waferBankDataMap.clear();
            m_waferBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Wafer Store Lot information into database: " + se);
            throw se;
        }

        try
        {
            if ( m_sortBinDataMap.isEmpty() == false )
            {
                FabSortAdaptorDAO.saveSortBinData( null, m_sortBinDataMap.values().iterator() );
            }
            success = true;
            m_sortBinDataMap.clear();
        }
        catch(Exception e)
        {
            success = false;
            System.out.println(" Exception Occrred While Saving the Sort Binning Info " +e );
            throw e;
        }
        // to save the Sort lot tables
        try
        {
            if ( m_sortDataMap.isEmpty() == false )
            {
                log( "Updating Sort Lot Information" );
                FabSortAdaptorDAO.saveSortLot( null, m_sortDataMap.values().iterator(),WIP_TYPE_SORT, VENDOR_NAME_SIGURD );
                success = true;
            }
            else
            {
                FabSortAdaptorDAO.closeFabSortLot( null,WIP_TYPE_SORT, VENDOR_NAME_SIGURD );
            }
            success = true;
        }
        catch ( Exception e )
        {
            success = false;
            log( "ERROR!! ERROR!! ERROR!! Could not update Sigurd Sort Data information into database: " + e );
            e.printStackTrace();
            throw e;
        }
        try
        {
            // Save Sort Ship Information
            if ( m_sortShipDataMap.isEmpty() == false )
            {
                log( " Updating Sort Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_sortShipDataMap.values().iterator() );
            }
            success = true;
            m_sortShipDataMap.clear();
            m_sortShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Sort Shipping Lot information into database: " + se );
            throw se;
        }
        try
        {
           if ( m_preTestDataMap.isEmpty() == false )
           {
               log(" Updating Class Stroe Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_preTestDataMap.values().iterator(), LOCATION_TYPE_PRE_TEST, VENDOR_NAME_SIGURD );
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_PRE_TEST, VENDOR_NAME_SIGURD  );
           }
           success = true;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Pre Test Lot information into database: " + se);
            throw se;
        }
		try
        {
           // Saving FINAL TEST Information
           if ( m_testDataMap.isEmpty() == false )
            {
                log( " Updating Test Lot Information" );
                TestAdaptorDAO.saveTestLot( dbTransaction,m_testDataMap.values().iterator(), LOCATION_TYPE_FT, VENDOR_NAME_SIGURD );
            }
            else
           {
               TestAdaptorDAO.closeTestLot( dbTransaction, LOCATION_TYPE_FT, VENDOR_NAME_SIGURD );
           }

           success = true;
        }
        catch ( ScalarException se )
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update FT Lot information into database: " + se );
            throw se;
        }
        try
        {
            // Save Sort Ship Information
            if ( m_testShipDataMap.isEmpty() == false )
            {
                log( " Updating FT Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_testShipDataMap.values().iterator() );
            }
            success = true;
            m_testShipDataMap.clear();
            m_testShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update FT Shipping Lot information into database: " + se );
            throw se;
        }
        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications( VENDOR_NAME_SIGURD,true, true, true );

        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots( VENDOR_NAME_SIGURD );

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( SigurdPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);
        // transaction 9
        try
        {
            if (success)
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SigurdPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println("SUCCESSFULLY UPDATED IN THE DATABASE");
            }
        }
        catch (ScalarException se)
        {
            success = false;  //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SigurdPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log("WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se);
            throw se;
        }

        // Step 10
        // Move the successful files to 'done' directory
        if (success)
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir);
        }
    }

    private void parseTestWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        InventoryLot inventoryLot  = null;
        TestLot      testLot       = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String vendorLotNumber  = null;
        String dateIn           = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);

            log( "\nFound " + fileName );
            log( "\nParsing the file for LFT Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for LFT Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("MfgPartNumber");
                fabLotNumber    = result.getString("CustomerLotNo");
                vendorLotNumber = result.getString("LotNo");
                if ( vendorLotNumber == null )
                {
                    vendorLotNumber = fabLotNumber;
                }
                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    if ( result.getDate( "WipDate") != null )
                    {
                        dateIn = StringUtils.formatDateToString( result.getDate( "WipDate"), "MM/dd/yyyy HH:mm:ss" );
                    }
                    else if ( result.getDate( "ReceivingDate") != null )
                    {
                        dateIn = StringUtils.formatDateToString( result.getDate( "ReceivingDate"), "MM/dd/yyyy HH:mm:ss" );
                    }
                    if ( result.getInt("BANK") > 0 )
                    {
                        log("IN Pre Test = " + vendorLotNumber);
                        inventoryLot = m_preTestDataMap.get( vendorLotNumber + fabLotNumber + partNumber );

                        if( inventoryLot == null )
                        {
                            inventoryLot = new InventoryLot( VENDOR_NAME_SIGURD,  LOCATION_TYPE_PRE_TEST);
                            m_preTestDataMap.put( vendorLotNumber + fabLotNumber + partNumber , inventoryLot );
                        }

                        inventoryLot.setPartNumber( partNumber );
                        inventoryLot.setLotNumber( vendorLotNumber );
                        inventoryLot.setFabLotNumber( fabLotNumber );
                        inventoryLot.setQtyIn( result.getInt("BANK") +"" );
                        inventoryLot.setDateIn( dateIn );
                        inventoryLot.setStatus(LOT_STATUS_RUNNING);
                        inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                    }

                    if( result.getInt("IQC") > 0 || result.getInt("PM") > 0 || result.getInt("F/T") > 0 || result.getInt("EQC") > 0
                     || result.getInt("WAIT") > 0 || result.getInt("L(B)/S") > 0 || result.getInt("B/I") > 0 || result.getInt("VB") > 0
                     || result.getInt("T/R") > 0 || result.getInt("V/M") > 0 || result.getInt("FQC") > 0 || result.getInt("BAKE") > 0
                     || result.getInt("PACK") > 0|| result.getInt("OQC") > 0 || result.getInt("DS") > 0 || result.getInt("MT") > 0 || result.getInt("INV") > 0 )
                    {
                        log("In FT" + vendorLotNumber );
                        testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                        if (testLot == null)
                        {
                            testLot = new TestLot( VENDOR_NAME_SIGURD);
                            m_testDataMap.put( vendorLotNumber + fabLotNumber + partNumber, testLot);
                        }

                        if (result.getInt("IQC") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt(testLot.getQtyIn()) + result.getInt("IQC") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("IQC")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("IQC")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", IQC");
                            }
                            else
                            {
                                testLot.setStage("IQC");
                            }
                        }
                        if (result.getInt("PM") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("PM") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("PM")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("PM")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", PM");
                            }
                            else
                            {
                                testLot.setStage("PM");
                            }
                        }
                        if (result.getInt("F/T") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt(testLot.getQtyIn()) + result.getInt("F/T") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("F/T")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("F/T")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", F/T");
                            }
                            else
                            {
                                testLot.setStage("F/T");
                            }
                        }
                        if (result.getInt("EQC") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("EQC") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("EQC")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("EQC")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", EQC");
                            }
                            else
                            {
                                testLot.setStage("EQC");
                            }
                        }
                        if (result.getInt("WAIT") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt(testLot.getQtyIn()) + result.getInt("WAIT") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("WAIT")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("WAIT")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", WAIT");
                            }
                            else
                            {
                                testLot.setStage("WAIT");
                            }
                        }
                        if (result.getInt("L(B)/S") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt(testLot.getQtyIn()) + result.getInt("L(B)/S") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("L(B)/S")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("L(B)/S")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", L(B)/S");
                            }
                            else
                            {
                                testLot.setStage("L(B)/S");
                            }
                        }
                        if (result.getInt("B/I") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("B/I") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("B/I")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("B/I")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", B/I");
                            }
                            else
                            {
                                testLot.setStage("B/I");
                            }
                        }
                        if (result.getInt("VB") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("VB") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("VB")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("VB")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", VB");
                            }
                            else
                            {
                                testLot.setStage("VB");
                            }
                        }
                        if (result.getInt("T/R") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("T/R") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("T/R")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("T/R")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", T/R");
                            }
                            else
                            {
                                testLot.setStage("T/R");
                            }
                        }
                        if (result.getInt("V/M") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("V/M") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("V/M")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("V/M")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", V/M");
                            }
                            else
                            {
                                testLot.setStage("V/M");
                            }
                        }
                        if (result.getInt("FQC") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("FQC") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("FQC")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("FQC")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", FQC");
                            }
                            else
                            {
                                testLot.setStage("FQC");
                            }
                        }
                        if (result.getInt("BAKE") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("BAKE") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("BAKE")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("BAKE")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", BAKE");
                            }
                            else
                            {
                                testLot.setStage("BAKE");
                            }
                        }
                        if (result.getInt("PACK") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( Integer.parseInt(testLot.getQtyIn()) + result.getInt("PACK") +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("PACK")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("PACK")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", PACK");
                            }
                            else
                            {
                                testLot.setStage("PACK");
                            }
                        }
                        if (result.getInt("OQC") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("OQC") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("OQC")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("OQC")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", OQC");
                            }
                            else
                            {
                                testLot.setStage("OQC");
                            }
                        }
                        if (result.getInt("DS") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("DS") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("DS")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("DS")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", DS");
                            }
                            else
                            {
                                testLot.setStage("DS");
                            }
                        }
                        if (result.getInt("MT") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("MT") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("MT")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("MT")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", MT");
                            }
                            else
                            {
                                testLot.setStage("MT");
                            }
                        }
                        if (result.getInt("INV") > 0 )
                        {
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot.getQtyIn() != null)
                            {
                                try
                                {
                                    testLot.setQtyIn( (Integer.parseInt(testLot.getQtyIn()) + result.getInt("INV") ) +"" );
                                }
                                catch (Exception e)
                                {
                                    testLot.setQtyIn(result.getInt("INV")+"");
                                }
                            }
                            else
                            {
                                testLot.setQtyIn(result.getInt("INV")+"");
                            }

                            if (testLot.getStage() != null)
                            {
                                testLot.setStage(testLot.getStage() + ", INV");
                            }
                            else
                            {
                                testLot.setStage("INV");
                            }
                        }
                        if ( testLot.getQtyIn() != null && !"0".equalsIgnoreCase( testLot.getQtyIn() ) )
                        {
                            testLot.setLotNumber( vendorLotNumber );
                            testLot.setPartNumber( partNumber );
                            if ( "HOLD".equalsIgnoreCase(result.getString("Status") ) )
                            {
                                testLot.setStatus( LOT_STATUS_HOLD );
                            }
                            else
                            {
                                testLot.setStatus( LOT_STATUS_RUNNING );
                            }
                            testLot.setFabLotNumber( fabLotNumber );
                            testLot.setPsType( LOCATION_TYPE_FT );
                            testLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                            testLot.setDateIn( dateIn );
                            testLot.setPoNumber( result.getString("CustomerFormNo"));
                            if ( result.getDate("RSD") != null )
                            {
                                testLot.setExpecetedDate( StringUtils.formatDateToString(result.getDate("RSD"), "MM/dd/yyyy HH:mm:ss"));
                            }
                        }
                    }
                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private void parseCPWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        InventoryLot inventoryLot  = null;
        FabSortLot   sortLot      = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String dateIn           = null;


        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("ProductNo");
                fabLotNumber    = result.getString("CustomerLotNo");

                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    if ( result.getDate( "WipDate") != null )
                    {
                        dateIn = StringUtils.formatDateToString( result.getDate( "WipDate"), "MM/dd/yyyy HH:mm:ss" );
                    }
                    else if ( result.getDate( "ReceivingDate") != null )
                    {
                        dateIn = StringUtils.formatDateToString( result.getDate( "ReceivingDate"), "MM/dd/yyyy HH:mm:ss" );
                    }

                    if (result.getInt("WIQC") > 0 || result.getInt("WP/M") > 0 || result.getInt("CPBI") > 0 || result.getInt("CP0") > 0
                         || result.getInt("CP") > 0 || result.getInt("CP1") > 0 || result.getInt("LR") > 0 || result.getInt("CP2") > 0
                         || result.getInt("CP3") > 0 || result.getInt("WVM") > 0 || result.getInt("CPX") > 0 || result.getInt("D/P") > 0
                         || result.getInt("INK") > 0 || result.getInt("BAKE") > 0 || result.getInt("WQVM") > 0 || result.getInt("PACK") > 0
                         || result.getInt("OQC") > 0 || result.getInt("UV-ERASE ") > 0 || result.getInt("INV") > 0 )
                    {
                        log("In sort" + fabLotNumber );
                        sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                        if (sortLot == null)
                        {
                            sortLot = new FabSortLot( VENDOR_NAME_SIGURD, WIP_TYPE_SORT );
                            m_sortDataMap.put( fabLotNumber + partNumber, sortLot);
                        }
                        if (result.getInt("WIQC") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("WIQC") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("WIQC")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("WIQC")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", WIQC");
                            }
                            else
                            {
                                sortLot.setStage("WIQC");
                            }
                        }
                        if (result.getInt("WP/M") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("WP/M") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("WP/M")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("WP/M")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", WP/M");
                            }
                            else
                            {
                                sortLot.setStage("WP/M");
                            }
                        }
                        if (result.getInt("CPBI") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CPBI") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CPBI")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CPBI")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CPBI");
                            }
                            else
                            {
                                sortLot.setStage("CPBI");
                            }
                        }
                        if (result.getInt("CP0") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CP0") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CP0")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CP0")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CP0");
                            }
                            else
                            {
                                sortLot.setStage("CP0");
                            }
                        }
                        if (result.getInt("CP") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CP") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CP")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CP")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CP");
                            }
                            else
                            {
                                sortLot.setStage("CP");
                            }
                        }
                        if (result.getInt("CP1") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CP1") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CP1")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CP1")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CP1");
                            }
                            else
                            {
                                sortLot.setStage("CP1");
                            }
                        }
                        if (result.getInt("LR") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("LR") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("LR")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("LR")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", LR");
                            }
                            else
                            {
                                sortLot.setStage("LR");
                            }
                        }
                        if (result.getInt("CP2") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CP2") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CP2")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CP2")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CP2");
                            }
                            else
                            {
                                sortLot.setStage("CP2");
                            }
                        }
                        if (result.getInt("CP3") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CP3") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CP3")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CP3")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CP3");
                            }
                            else
                            {
                                sortLot.setStage("CP3");
                            }
                        }
                        if (result.getInt("WVM") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("WVM") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("WVM")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("WVM")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", WVM");
                            }
                            else
                            {
                                sortLot.setStage("WVM");
                            }
                        }
                        if (result.getInt("CPX") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("CPX") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("CPX")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("CPX")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", CPX");
                            }
                            else
                            {
                                sortLot.setStage("CPX");
                            }
                        }
                        if (result.getInt("D/P") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("D/P") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("D/P")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("D/P")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", D/P");
                            }
                            else
                            {
                                sortLot.setStage("D/P");
                            }
                        }
                        if (result.getInt("INK") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("INK") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("INK")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("INK")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", INK");
                            }
                            else
                            {
                                sortLot.setStage("INK");
                            }
                        }
                        if (result.getInt("BAKE") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("BAKE") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("BAKE")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("BAKE")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", BAKE");
                            }
                            else
                            {
                                sortLot.setStage("BAKE");
                            }
                        }
                        if (result.getInt("WQVM") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("WQVM") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("WQVM")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("WQVM")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", WQVM");
                            }
                            else
                            {
                                sortLot.setStage("WQVM");
                            }
                        }
                        if (result.getInt("PACK") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("PACK") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("PACK")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("PACK")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", PACK");
                            }
                            else
                            {
                                sortLot.setStage("PACK");
                            }
                        }
                        if (result.getInt("OQC") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("OQC") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("OQC")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("OQC")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", OQC");
                            }
                            else
                            {
                                sortLot.setStage("OQC");
                            }
                        }
                        if (result.getInt("UV-ERASE ") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("UV-ERASE ") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("UV-ERASE ")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("UV-ERASE ")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", UV-ERASE");
                            }
                            else
                            {
                                sortLot.setStage("UV-ERASE");
                            }
                        }
                        if (result.getInt("INV") > 0 )
                        {
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    sortLot.setQtyIn( Integer.parseInt(sortLot.getQtyIn()) + result.getInt("INV") +"" );
                                }
                                catch (Exception e)
                                {
                                    sortLot.setQtyIn(result.getInt("INV")+"");
                                }
                            }
                            else
                            {
                                sortLot.setQtyIn(result.getInt("INV")+"");
                            }

                            if (sortLot.getStage() != null)
                            {
                                sortLot.setStage(sortLot.getStage() + ", INV");
                            }
                            else
                            {
                                sortLot.setStage("INV");
                            }
                        }
                        if ( sortLot.getQtyIn() != null && !"0".equalsIgnoreCase( sortLot.getQtyIn() ) )
                        {
                            sortLot.setLotId( fabLotNumber );
                            sortLot.setCustProduct( partNumber );
                            if ( "HOLD".equalsIgnoreCase(result.getString("Status") ) || "Wait".equalsIgnoreCase(result.getString("Status")) )
                            {
                                sortLot.setLotStatus( LOT_STATUS_HOLD );
                            }
                            else
                            {
                                sortLot.setLotStatus( LOT_STATUS_RUNNING );
                            }
                            sortLot.setFabLotNumber( fabLotNumber );
                            sortLot.setPsType( LOCATION_TYPE_SORT );
                            sortLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                            sortLot.setDateIn( dateIn );
                            sortLot.setPoNumber( result.getString("CustomerFormNo") );
                            if ( result.getDate( "RSD") != null )
                            {
                                sortLot.setForcastedDateOut( StringUtils.formatDateToString( result.getDate( "RSD"), "MM/dd/yyyy HH:mm:ss" ));
                            }
                        }
                    }
                    else
                    {
                        log("IN WaferStore lotNumber = " + fabLotNumber);
                        inventoryLot = m_waferBankDataMap.get( fabLotNumber + partNumber );

                        if( inventoryLot == null )
                        {
                            inventoryLot = new InventoryLot( VENDOR_NAME_SIGURD,  LOCATION_TYPE_WAFER_STORE);
                            m_waferBankDataMap.put(  fabLotNumber + partNumber , inventoryLot );
                        }
                        int quantity = 0;
                        if (inventoryLot.getWaferQtyIn() != null)
                        {
                            try
                            {
                                quantity = Integer.parseInt( inventoryLot.getWaferQtyIn() );
                            }
                            catch (NumberFormatException npe) {
                            }
                            try
                            {
                                quantity += result.getInt("WaferQty");
                            }
                            catch (NumberFormatException npe) {
                            }
                        }
                        else
                        {
                            try
                            {
                                quantity = result.getInt("WaferQty");
                            }
                            catch (NumberFormatException npe) {
                            }
                        }
                        if ( quantity > 0 )
                        {
                            inventoryLot.setPartNumber( partNumber );
                            inventoryLot.setLotNumber( fabLotNumber );
                            inventoryLot.setFabLotNumber( fabLotNumber );
                            inventoryLot.setWaferQtyIn( quantity+"" );
                            inventoryLot.setDateIn( dateIn );
                            inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                            inventoryLot.setCustomerPO( result.getString("CustomerFormNo") );
                            if ( "HOLD".equalsIgnoreCase(result.getString("Status") ) || "Wait".equalsIgnoreCase(result.getString("Status")) )
                            {
                                inventoryLot.setStatus( LOT_STATUS_HOLD );
                            }
                            else
                            {
                                inventoryLot.setStatus( LOT_STATUS_RUNNING );
                            }
                        }
                    } //Else Block
                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }
    
    private void parseCPShipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        ShippedLot  shippedLot = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String shipDate           = null;
        String nextLocation         = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Ship Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Ship Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("FG Part #");
                fabLotNumber    = result.getString("Ikanos Lot #");

                if (result.getDate("SHIPDATE") != null )
                {
                   shipDate =  StringUtils.formatDateToString(result.getDate("SHIPDATE"), "MM/dd/yyyy HH:mm:ss" );
                }
                nextLocation = result.getString("Ship To");

                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    shippedLot = ( ShippedLot ) m_sortShipDataMap.get ( partNumber + fabLotNumber + nextLocation + shipDate );

                   if ( shippedLot == null )
                   {
                        shippedLot = new ShippedLot(VENDOR_NAME_SIGURD, LOCATION_TYPE_SORT);
                        m_sortShipDataMap.put( partNumber + fabLotNumber + nextLocation + shipDate, shippedLot );
                   }

                   int quantity = 0;
                   int dquantity = 0;
                   if ( shippedLot.getShipQty() != null )
                   {
                       try
                       {
                           dquantity = Integer.parseInt( shippedLot.getShipDieQty() );
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                           dquantity =0;
                       }

                       quantity += result.getInt("SHIP Wafer Qty") ;
                       dquantity += result.getInt("Good Die");
                   }
                   else
                   {
                       quantity += result.getInt("SHIP Wafer Qty") ;
                       dquantity += result.getInt("Good Die");
                   }

                   String waferIds = null;
                   if ( shippedLot.getWaferID() != null )
                   {
                         waferIds =  shippedLot.getWaferID();
						if( result.getString(" Wafer ID #") != null && result.getString(" Wafer ID #").trim().length() > 0 )
						{
							waferIds = waferIds + ";" + result.getString(" Wafer ID #").replaceAll("\u3001",";");
						}
                   }
                   else
                   {
						if( result.getString(" Wafer ID #") != null && result.getString(" Wafer ID #").trim().length() > 0 )
						{
	                       waferIds = result.getString(" Wafer ID #").replaceAll("\u3001",";");
						}
                   }

                   if ( quantity > 0 )
                   {
                       shippedLot.setLotNumber( fabLotNumber );
                       shippedLot.setPartNumber( partNumber );
                       shippedLot.setShipDate( shipDate );
                       shippedLot.setShipQty( quantity + "" );
                       shippedLot.setNextLocation( nextLocation );
                       shippedLot.setWaferID( waferIds );
                       shippedLot.setFabLotNumber( fabLotNumber );
                       shippedLot.setProcessType( LOCATION_TYPE_SORT );
                       shippedLot.setShipDieQty( dquantity + "");
                       shippedLot.setShippingDoc( result.getString("CustomerFormNo"));
                   }
                
                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }
    private void parseFTShipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        ShippedLot  shippedLot = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String lotNumber     = null;
        String shipDate           = null;
        String nextLocation         = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);

            log( "\nFound " + fileName );
            log( "\nParsing the file for FT Ship Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FT Ship Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("FG Part #");
                fabLotNumber    = result.getString("Ikanos Lot #");
                lotNumber       = result.getString("Subcon Lot #");
                
                if (result.getDate("SHIPDATE") != null )
                {
                   shipDate =  StringUtils.formatDateToString(result.getDate("SHIPDATE"), "MM/dd/yyyy HH:mm:ss" );
                }
                nextLocation = result.getString("Ship To");

                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    shippedLot = ( ShippedLot ) m_testShipDataMap.get ( partNumber + lotNumber+ fabLotNumber + nextLocation + shipDate );

                   if ( shippedLot == null )
                   {
                        shippedLot = new ShippedLot(VENDOR_NAME_SIGURD, LOCATION_TYPE_FT);
                        m_testShipDataMap.put( partNumber +lotNumber+ fabLotNumber + nextLocation + shipDate, shippedLot );
                   }

                   int quantity = 0;

                   if ( shippedLot.getShipQty() != null )
                   {
                       try
                       {
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }

                       quantity += result.getInt("SHIP_DIE_QTY") ;
                   }
                   else
                   {
                       quantity += result.getInt("SHIP_DIE_QTY") ;
                   }

                   if ( quantity > 0 )
                   {
                       shippedLot.setLotNumber( lotNumber );
                       shippedLot.setPartNumber( partNumber );
                       shippedLot.setShipDate( shipDate );
                       shippedLot.setShipQty( quantity + "" );
                       shippedLot.setNextLocation( nextLocation );
                       shippedLot.setFabLotNumber( fabLotNumber );
                       shippedLot.setProcessType( LOCATION_TYPE_FT );
                       shippedLot.setShippingDoc( result.getString("CustomerFormNo"));
                   }

                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private ArrayList<TestLotBinReport> parseFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FT Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);
            
            result = parser.parse();

            while ( result.next() )
            {
                partNumber = result.getString("DEVICE");
                fablotNumber = result.getString("Ikanons Lot No");
                lotNumber   =  result.getString("Subcon Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("L/C") );
                    ftYieldStatus.setStage( result.getString("INSERTION") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Program No"));
                    ftYieldStatus.setTesterId( result.getString("Tester ID") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler ID") );
                    ftYieldStatus.setLb(result.getString("L/B ID"));
                    ftYieldStatus.setTemp( result.getString("Temp"));
                    ftYieldStatus.setDateIn( StringUtils.formatDate( result.getString("Test In Date"), "MM/dd/yyyy HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setDateOut(StringUtils.formatDate( result.getString("Test Out Date"), "MM/dd/yyyy HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ));
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Out QTY")+"" );
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );
                    ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getString("Bin1") );
                    ftYieldStatus.setBin2(result.getString("Bin2"));
                    ftYieldStatus.setBin3(result.getString("Bin3"));
                    ftYieldStatus.setBin4(result.getString("Bin4"));
                    ftYieldStatus.setBin5(result.getString("Bin5"));
                    ftYieldStatus.setBin6(result.getString("Bin6"));
                    ftYieldStatus.setBin7(result.getString("Bin7"));
                    ftYieldStatus.setBin8(result.getString("Bin8"));
                    ftYieldStatus.setLossQty( result.getString("Loss QTY") );
                    ftYieldStatus.setVmQty( result.getString("VM QTY") );
                    ftYieldStatus.setOsQty( result.getString("OSQTY") );
                    
                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the Sigurd FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }

    private ArrayList<SortLotBinData> parseCPBinReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        FabSortLot sortBinTotalLot = null;
        FabSortLot sortBinLot   = null;
        ArrayList<SortLotBinData> cpYieldList  = new ArrayList<SortLotBinData>();
         int waferId             = 0;
        HashMap waferIdMap = new HashMap();
        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(3);
            ((XLSParserImpl)parser).setDataStartRowIdx(4);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );
             while ( result.next() )
            {
                partNumber = result.getString("Device");
                lotNumber = result.getString("Ikanons Lot No");
                waferId   = result.getInt("Wafer #");

                if (partNumber!= null && partNumber.trim().length()>0 && lotNumber != null && lotNumber.trim().length() > 0 && waferId > 0 && waferId < 26 )
                {
                    sortBinTotalLot = (FabSortLot) m_sortTotalMap.get( partNumber + lotNumber);

                    if ( sortBinTotalLot == null )
                    {
                        sortBinTotalLot = new FabSortLot( VENDOR_NAME_SIGURD, LOCATION_TYPE_SORT );
                        m_sortTotalMap.put( partNumber + lotNumber  , sortBinTotalLot );
                    }

                    int m_totalProbeQty         = 0;
                    int m_totalPassQty          = 0;
                    int m_waferQtyIn            = 0;
                    if ( sortBinTotalLot.getQtyIn() != null )
                    {
                       try
                       {
                           m_waferQtyIn = Integer.parseInt( sortBinTotalLot.getQtyIn() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                       }

                       m_totalPassQty = sortBinTotalLot.getTotalgoodDieQtyIn();
                       m_totalProbeQty = sortBinTotalLot.getTotalprobeQtyIn();

                       if ( !waferIdMap.containsKey( partNumber + lotNumber + waferId ) )
                       {
                           waferIdMap.put( partNumber + lotNumber+waferId, waferId );
                            m_waferQtyIn += 1;

                            m_totalPassQty += result.getInt("good Dies");
                            m_totalProbeQty += result.getInt("GDPW");
                       }
                    }
                    else
                    {
                        if ( !waferIdMap.containsKey( partNumber + lotNumber+waferId ) )
                        {
                            waferIdMap.put( partNumber + lotNumber+waferId, waferId );
                            m_waferQtyIn = 1;
                            m_totalPassQty = result.getInt("good Dies");
                            m_totalProbeQty = result.getInt("GDPW");
                        }
                    }
                    if ( sortBinTotalLot.getDateOut() != null && result.getString("Test Out Date") != null )
                    {
                         Date tempDate1 = StringUtils.format(sortBinTotalLot.getDateOut());
                         Date tempDate2 = StringUtils.format(result.getString("Test Out Date") );

                        if ( tempDate1.compareTo( tempDate2 ) < 0 )
                        {
                            sortBinTotalLot.setDateOut( result.getString("Test Out Date") );
                        }
                    }
                    else
                    {
                        sortBinTotalLot.setDateOut( result.getString("Test Out Date") );
                    }
                    sortBinTotalLot.setQtyIn( m_waferQtyIn+"");
                    sortBinTotalLot.setTotalgoodDieQtyIn( m_totalPassQty);
                    sortBinTotalLot.setTotalprobeQtyIn( m_totalProbeQty );
                }
            }
            //FOR Restting the Row Index
            result.reset();

            while ( result.next() )
            {
                partNumber   = result.getString("Device");
                fablotNumber = result.getString("Ikanons Lot No");
                lotNumber    = result.getString("Ikanons Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }
                waferId   = result.getInt("Wafer #");
                
                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 && waferId > 0 && waferId < 26 )
                {
                    SortLotBinData cpYieldStatus = new SortLotBinData();

                    sortBinLot = ( FabSortLot ) m_sortBinDataMap.get( partNumber + lotNumber + waferId);

                    if ( sortBinLot == null )
                    {
                        sortBinLot = new FabSortLot( VENDOR_NAME_SIGURD, LOCATION_TYPE_SORT );
                        m_sortBinDataMap.put( partNumber + lotNumber + waferId  , sortBinLot );
                    }
                    sortBinLot.setCustProduct( partNumber );
                    sortBinLot.setLotId( lotNumber );
                    sortBinLot.setFabLotNumber( lotNumber );
                    sortBinLot.setWaferID( waferId+"" );
                     // WAFER QTY IN Always Same(total wafers) ,But For Each every Wafer update the Wafer Qty, the the WaferQty
                    //System.out.println("DATE OUT = " + m_sortTotalMap.get(partNumber + lotNumber).getDateOut() );
                    sortBinLot.setQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getQtyIn() +"");
                    sortBinLot.setTotalgoodDieQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getTotalgoodDieQtyIn() );
                    sortBinLot.setTotalprobeQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getTotalprobeQtyIn() );
                    sortBinLot.setDateOut( result.getString("Test Out Date") );
                    sortBinLot.setDateIn( result.getString("Test In Date") );
                    sortBinLot.setTesterNo( result.getString("Tester ID") );
                    sortBinLot.setTestProgram( result.getString("Program No") );
                    sortBinLot.setGoodDieQtyIn(result.getInt("good Dies"));
                    sortBinLot.setRejectedQtyIn(result.getInt("GDPW")-result.getInt("good Dies"));
                    sortBinLot.setShipYield(result.getString("Yield").replaceAll("%","") );
                    sortBinLot.setTotalYield( 0 ); // if sets as 0, in the PROC Yield Calculating (INQTY *100.00/OUTQTY)
                    sortBinLot.setBinNumber( "Bin1" );

                    cpYieldStatus.setProduct( partNumber );
                    cpYieldStatus.setLotNumber(lotNumber );
                    cpYieldStatus.setFablotNumber( fablotNumber );
                    cpYieldStatus.setTemp(result.getString("Temperature"));
                    cpYieldStatus.setProbeId(result.getString("Prober ID"));
                    cpYieldStatus.setProbeCard(result.getString("Probe Card"));
                    cpYieldStatus.setInsertion(result.getString("Insertion"));
                    cpYieldStatus.setGrossDice( result.getInt("GDPW")+"");
                    cpYieldStatus.setGoodDice( result.getInt("good Dies")+"");
                    cpYieldStatus.setShipYield( result.getString("Yield").replaceAll("%","") );
                    cpYieldStatus.setWaferId( waferId+"");
                    cpYieldStatus.setDateIn( result.getString("Test In Date") );
                    int[] binQty = new int[100];
                    int binNo = 0;
                    for(int i=1; i<=100; i++ )
                    {
                        binQty[binNo++] = result.getInt("BIN"+i);
                    }
                    cpYieldStatus.setBinQty( binQty );

                    cpYieldList.add( cpYieldStatus );
                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the Sigurd CP Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return cpYieldList;
    }
    private void log( String message )
    {
      if ( message == null ) return;

      if ( m_parserLogger != null )
      {
          m_parserLogger.logDebug( message + LINE_SEPARATOR );
      }
      else
      {
          System.out.println( message );
      }
    } // end of log method

}

